﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Dal
{
    public static class SqlQueryForDataTatableClass
    {
        /// <summary>
        /// EF SQL 语句返回 dataTable
        /// </summary>
        /// <param name="db"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static DataTable SqlQueryForDataTatable(this Database db,
                 string sql,
                 SqlParameter[] parameters)
        {

            //SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            //conn.ConnectionString = db.Connection.ConnectionString;
            //if (conn.State != ConnectionState.Open)
            //{
            //    conn.Open();
            //}
            SqlConnection conn = (SqlConnection)db.Connection;

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;

            if (parameters.Length > 0)
            {
                foreach (var item in parameters)
                {
                    cmd.Parameters.Add(item);
                }
            }

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            return table;
        }

        public static DataTable DataTable(this Database db, CommandType commandType, string sql, SqlParameter[] parameters)
        {
            SqlConnection conn = (SqlConnection)db.Connection;

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = commandType;
            cmd.CommandText = sql;

            if (parameters.Length > 0)
            {
                foreach (var item in parameters)
                {
                    cmd.Parameters.Add(item);
                }
            }

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Parameters.Clear();
            return table;
        }

        public static DataSet DataSet(this Database db, CommandType commandType, string sql, SqlParameter[] parameters)
        {
            SqlConnection conn = (SqlConnection)db.Connection;

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = commandType;
            cmd.CommandText = sql;

            if (parameters.Length > 0)
            {
                foreach (var item in parameters)
                {
                    cmd.Parameters.Add(item);
                }
            }

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            cmd.Parameters.Clear();
            return ds;
        }
    }

    public class TableDal
    {
        public TableDal(BabyfaceEntities _db)
        {
            db = _db;
        }
        public BabyfaceEntities db { get; set; }

        #region (分页)
        /// <summary>
        /// 存储过程分页允许多字段排序
        /// </summary>
        /// <param name="page"></param>
        /// <param name="Count"></param>
        /// <returns></returns>
        public DataTable GetData_Page_FldsOrder(Model_MakePages page, ref int RecordCount)
        {
            SqlParameter[] parms = {
                new SqlParameter("@tblName",SqlDbType.VarChar,255),
                new SqlParameter("@fldNames",SqlDbType.VarChar,1000),
                new SqlParameter("@selectOrderFldName",SqlDbType.VarChar,500),
                new SqlParameter("@orderFldDesc",SqlDbType.VarChar,500),
                new SqlParameter("@keyFldName",SqlDbType.VarChar,255),
                new SqlParameter("@PageSize",SqlDbType.Int),
                new SqlParameter("@PageIndex",SqlDbType.Int),
                new SqlParameter("@strWhere",SqlDbType.VarChar,1000)
            };

            parms[0].Value = page.tblName;
            parms[1].Value = page.fldNames;
            parms[2].Value = page.selectOrderFldName;
            parms[3].Value = page.orderFldDesc;
            parms[4].Value = page.keyFldName;
            parms[5].Value = page.PageSize;
            parms[6].Value = page.PageIndex;
            parms[7].Value = page.strWhere;

            DataTable dt = new DataTable();
            //DataSet ds = SQLHelper.ExecuteDataset(SQLHelper.SqlConnectionString, CommandType.StoredProcedure, "sp_COMM_GetData_Page_FldsOrder", parms);
            DataSet ds = db.Database.DataSet(CommandType.StoredProcedure, "sp_COMM_GetData_Page_FldsOrder", parms);
            if (ds != null && ds.Tables.Count > 1)
            {
                RecordCount = int.Parse(ds.Tables[1].Rows[0][0].ToString());
            }
            else
            {
                RecordCount = 0;
            }
            //return ds.Tables[0];
            dt = ds.Tables[0];
            return dt;
        }
        #endregion

        public PageTable GetData_Page_FldsOrder(Model_MakePages page)
        {
            SqlParameter[] parms = {
                new SqlParameter("@tblName",SqlDbType.VarChar,255),
                new SqlParameter("@fldNames",SqlDbType.VarChar,1000),
                new SqlParameter("@selectOrderFldName",SqlDbType.VarChar,500),
                new SqlParameter("@orderFldDesc",SqlDbType.VarChar,500),
                new SqlParameter("@keyFldName",SqlDbType.VarChar,255),
                new SqlParameter("@PageSize",SqlDbType.Int),
                new SqlParameter("@PageIndex",SqlDbType.Int),
                new SqlParameter("@strWhere",SqlDbType.VarChar,1000)
            };

            parms[0].Value = page.tblName;
            parms[1].Value = page.fldNames;
            parms[2].Value = page.selectOrderFldName;
            parms[3].Value = page.orderFldDesc;
            parms[4].Value = page.keyFldName;
            parms[5].Value = page.PageSize;
            parms[6].Value = page.PageIndex;
            parms[7].Value = page.strWhere;

            DataSet ds = db.Database.DataSet(CommandType.StoredProcedure, "sp_COMM_GetData_Page_FldsOrder", parms);

            long totalCount = 0;
            if (ds != null && ds.Tables.Count > 1)
            {
                totalCount = long.Parse(ds.Tables[1].Rows[0][0].ToString());
            }
            else
            {
                totalCount = 0;
            }
            DataTable dt = ds.Tables[0];
            PageTable pageTable = new PageTable(dt, page.PageIndex, page.PageSize, totalCount);

            return pageTable;
        }
    }
}
